package cn.stylefeng.guns.modular.kh.service.impl;

import cn.stylefeng.guns.base.pojo.page.LayuiPageFactory;
import cn.stylefeng.guns.base.pojo.page.LayuiPageInfo;
import cn.stylefeng.guns.modular.basic.entity.BasicCustomer;
import cn.stylefeng.guns.modular.basic.entity.BasicPosition;
import cn.stylefeng.guns.modular.basic.entity.BasicPutgoods;
import cn.stylefeng.guns.modular.basic.entity.BasicWarehouse;
import cn.stylefeng.guns.modular.basic.service.BasicCustomerService;
import cn.stylefeng.guns.modular.basic.service.BasicPositionService;
import cn.stylefeng.guns.modular.basic.service.BasicPutgoodsService;
import cn.stylefeng.guns.modular.basic.service.BasicWarehouseService;
import cn.stylefeng.guns.modular.hx.basic.entity.HxBasicGoods;
import cn.stylefeng.guns.modular.hx.basic.service.HxBasicGoodsService;
import cn.stylefeng.guns.modular.hx.dps.entity.DpsZcServer;
import cn.stylefeng.guns.modular.hx.dps.service.DpsZcServerService;
import cn.stylefeng.guns.modular.hx.order.entity.HxOrderOutExcelItem;
import cn.stylefeng.guns.modular.hx.order.utils.JdbcUtls;
import cn.stylefeng.guns.modular.hx.order.utils.MssqlUtils;
import cn.stylefeng.guns.modular.hx.storage.entity.HxStorageStock;
import cn.stylefeng.guns.modular.hx.storage.service.HxStorageStockService;
import cn.stylefeng.guns.modular.kh.entity.KhOrderOut;
import cn.stylefeng.guns.modular.kh.entity.KhOrderOutItem;
import cn.stylefeng.guns.modular.kh.entity.KhWare;
import cn.stylefeng.guns.modular.kh.mapper.KhOrderOutMapper;
import cn.stylefeng.guns.modular.kh.model.params.DownDpsIby;
import cn.stylefeng.guns.modular.kh.model.params.KhOrderOutParam;
import cn.stylefeng.guns.modular.kh.model.params.KhTzStorageOrderItemParam;
import cn.stylefeng.guns.modular.kh.model.params.KhTzStorageOrderParam;
import cn.stylefeng.guns.modular.kh.model.result.KhOrderOutItemResult;
import cn.stylefeng.guns.modular.kh.model.result.KhOrderOutResult;
import cn.stylefeng.guns.modular.kh.service.*;
import cn.stylefeng.guns.sys.modular.system.entity.User;
import cn.stylefeng.guns.sys.modular.system.service.UserService;
import cn.stylefeng.roses.core.reqres.response.ResponseData;
import cn.stylefeng.roses.core.util.ToolUtil;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import java.io.InputStream;
import java.io.Serializable;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.*;

/**
 * <p>
 * 提总订单 服务实现类
 * </p>
 *
 * @author zzh
 * @since 2025-10-21
 */
@Service
public class KhOrderOutServiceImpl extends ServiceImpl<KhOrderOutMapper, KhOrderOut> implements KhOrderOutService {

    @Resource
    private BasicCustomerService basicCustomerService;

    @Resource
    private BasicWarehouseService basicWarehouseService;

    @Resource
    private UserService userService;

    @Resource
    private HxBasicGoodsService hxBasicGoodsService;

    @Resource
    private BasicPutgoodsService basicPutgoodsService;

    @Resource
    private KhOrderOutItemService khOrderOutItemService;

    @Resource
    private BasicPositionService basicPositionService;

    @Resource
    private KhWareService khWareService;

    @Resource
    private DpsZcServerService dpsZcServerService;

    @Resource
    private HxStorageStockService hxStorageStockService;

    @Resource
    private KhTzStorageOrderService khTzStorageOrderService;

    @Resource
    private KhTzStorageOrderItemService khTzStorageOrderItemService;

    @Override
    public void add(KhOrderOutParam param) {
        KhOrderOut entity = getEntity(param);
        this.save(entity);
    }

    @Override
    public void delete(KhOrderOutParam param) {
        this.removeById(getKey(param));
    }

    @Override
    public void update(KhOrderOutParam param) {
        KhOrderOut oldEntity = getOldEntity(param);
        KhOrderOut newEntity = getEntity(param);
        ToolUtil.copyProperties(newEntity, oldEntity);
        this.updateById(newEntity);
    }

    @Override
    public KhOrderOutResult findBySpec(KhOrderOutParam param) {
        return null;
    }

    @Override
    public List<KhOrderOutResult> findListBySpec(KhOrderOutParam param) {
        return null;
    }

    @Override
    public LayuiPageInfo findPageBySpec(KhOrderOutParam param, String condition) {
        Page pageContext = getPageContext();
        IPage page = this.baseMapper.customPageList(pageContext, param, condition);
        return LayuiPageFactory.createPageInfo(page);
    }

    @Override
    @Transactional(rollbackFor = Exception.class, timeout = 600)
    public ResponseData getExceldata(MultipartFile file, KhOrderOutParam khOrderOutParam, ResponseData data) throws Exception {
        User user = userService.getcurrentUser();
        String fileName = file.getOriginalFilename();
        List<HxOrderOutExcelItem> list = new ArrayList<HxOrderOutExcelItem>();

        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            data.setMessage("上传文件格式不正确");
            data.setSuccess(false);
        }
        boolean isExcel2003 = true;
        if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
            isExcel2003 = false;
        }
        InputStream is = file.getInputStream();
        Workbook wb = null;
        if (isExcel2003) {
            wb = new HSSFWorkbook(is);
        } else {
            wb = new XSSFWorkbook(is);
        }
        Sheet sheet = wb.getSheetAt(0);
        // 获取客户信息
        BasicCustomer basicCustomer = basicCustomerService.getById(khOrderOutParam.getCustomerId());
        if (ToolUtil.isEmpty(basicCustomer)) {
            return ResponseData.error("客户不存在，无法操作");
        }

        BasicWarehouse basicWarehouse = basicWarehouseService.getById(khOrderOutParam.getSetWarehouseId());
        if (ToolUtil.isEmpty(basicWarehouse)) {
            return ResponseData.error("仓库不存在，无法操作");
        }

        // 商品信息
        List<HxBasicGoods> hxBasicGoodsList = hxBasicGoodsService.lambdaQuery()
                .eq(HxBasicGoods::getGoodsCustomerId, khOrderOutParam.getCustomerId())
                .list();
        Map<String, HxBasicGoods> hxBasicGoodsMap = new HashMap<>();
        for (HxBasicGoods obj : hxBasicGoodsList) {
            hxBasicGoodsMap.put(obj.getGoodsNumber(), obj);
        }

        // 门店信息
        List<BasicPutgoods> basicPutgoodsList = basicPutgoodsService.lambdaQuery()
                .eq(BasicPutgoods::getPutgoodsCustomerId, khOrderOutParam.getCustomerId())
                .eq(BasicPutgoods::getUnitType, "门店")
                .list();
        Map<String, BasicPutgoods> basicPutgoodsMap = new HashMap<>();
        for (BasicPutgoods obj : basicPutgoodsList) {
            basicPutgoodsMap.put(obj.getUnitNumber(), obj);
        }

        List<KhOrderOut> khOrderOutAddList = new ArrayList<>();
        List<KhOrderOutItem> khOrderOutItemAddList = new ArrayList<>();
        KhOrderOut khOrderOutAdd;
        KhOrderOutItem khOrderOutItemAdd;
        Map<String, KhOrderOut> khOrderOutMap = new HashMap<>();
        for (int r = 1; r <= sheet.getLastRowNum() + 1; r++) {
            Row row = sheet.getRow(r);
            if (row == null) {
                continue;
            }
            khOrderOutAdd = new KhOrderOut();
            khOrderOutItemAdd = new KhOrderOutItem();
            khOrderOutAdd.setCustomerId(basicCustomer.getId().longValue());
            khOrderOutAdd.setCustomerName(basicCustomer.getCustomerName());
            khOrderOutAdd.setOrderType(khOrderOutParam.getOrderType());
            khOrderOutAdd.setOrderState("待提交");
            khOrderOutAdd.setSetWarehouseId(basicWarehouse.getId().longValue());
            khOrderOutAdd.setSetWarehouse(basicWarehouse.getWarehouseName());
            khOrderOutAdd.setCreateUserName(user.getName());
            khOrderOutAdd.setCreateTime(new Date());
            khOrderOutAdd.setWorkType(khOrderOutParam.getWorkType());
            String peisongTime = getCellValue(row.getCell(0));
            if (ToolUtil.isNotEmpty(peisongTime)) {
                khOrderOutAdd.setPeisongTime(peisongTime);
            }

            String orderOutNumber = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(1));
            if (ToolUtil.isNotEmpty(orderOutNumber)) {
                khOrderOutAdd.setOrderOutNumber(orderOutNumber);
                khOrderOutItemAdd.setOrderOutNumber(orderOutNumber);
            } else {
                data.setMessage("订单号不能为空！");
                data.setSuccess(false);
                return data;
            }
            Integer count = this.lambdaQuery()
                    .eq(KhOrderOut::getCustomerId, khOrderOutParam.getCustomerId())
                    .eq(KhOrderOut::getOrderOutNumber, orderOutNumber)
                    .count();
            if (count > 0) {
                data.setMessage(orderOutNumber + "订单号已存在，不能重复导入！");
                data.setSuccess(false);
                return data;
            }
            // 收货客户编码
            String getUnitNumber = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(2));
            if (ToolUtil.isNotEmpty(getUnitNumber)) {
                khOrderOutAdd.setGetUnitNumber(getUnitNumber);
            } else {
                data.setMessage("收货客户编码不能为空！");
                data.setSuccess(false);
                return data;
            }
            if (ToolUtil.isEmpty(basicPutgoodsMap.get(getUnitNumber))) {
                data.setMessage(getUnitNumber + "收货客户不存在，请维护后重试！");
                data.setSuccess(false);
                return data;
            }
            khOrderOutAdd.setGetUnit(basicPutgoodsMap.get(getUnitNumber).getPutgoodsName());
            khOrderOutAdd.setGetUnitId(basicPutgoodsMap.get(getUnitNumber).getId().longValue());

            // 商品编码
            String goodsNumber = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(4));
            if (ToolUtil.isNotEmpty(goodsNumber)) {
                khOrderOutItemAdd.setGoodsNumber(goodsNumber);
            } else {
                data.setMessage("收货客户编码不能为空！");
                data.setSuccess(false);
                return data;
            }
            HxBasicGoods hxBasicGoods = hxBasicGoodsMap.get(goodsNumber);
            if (ToolUtil.isEmpty(hxBasicGoods)) {
                return ResponseData.error(goodsNumber + "商品不存在，请维护后重试！");
            }
            khOrderOutItemAdd.setGoodsId(hxBasicGoods.getId().longValue());
            khOrderOutItemAdd.setGoodsName(hxBasicGoods.getGoodsName());
            khOrderOutItemAdd.setGoodsHcode69(hxBasicGoods.getGoodsHcode69());
            khOrderOutItemAdd.setGoodsXcode69(hxBasicGoods.getGoodsXcode69());
            khOrderOutItemAdd.setGoodsPcode69(hxBasicGoods.getGoodsPcode69());
            khOrderOutItemAdd.setGoodsSpecs(hxBasicGoods.getGoodsSpecs());
            khOrderOutItemAdd.setDismantlingParts(hxBasicGoods.getDismantlingParts());
            khOrderOutItemAdd.setUnit(hxBasicGoods.getPsUnit());
            khOrderOutItemAdd.setRate(new BigDecimal(hxBasicGoods.getPsRate()));
            // 商品数量
            String psNum = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(6));
            if (ToolUtil.isNotEmpty(psNum)) {
                khOrderOutItemAdd.setPsNum(new BigDecimal(psNum));
            } else {
                data.setMessage("梳理不能为空！");
                data.setSuccess(false);
                return data;
            }
            khOrderOutItemAdd.setJibenUnit(hxBasicGoods.getJibenUnit());
            khOrderOutItemAdd.setJibenNum(khOrderOutItemAdd.getPsNum().multiply(khOrderOutItemAdd.getRate()));

            khOrderOutItemAddList.add(khOrderOutItemAdd);

            if (ToolUtil.isEmpty(khOrderOutMap.get(orderOutNumber))) {
                khOrderOutAddList.add(khOrderOutAdd);
                khOrderOutMap.put(orderOutNumber, khOrderOutAdd);
            }

        }

        if (khOrderOutAddList.size() > 0 && khOrderOutItemAddList.size() > 0) {
            khOrderOutItemService.saveBatch(khOrderOutItemAddList);
            this.saveBatch(khOrderOutAddList);
        }
        data.setSuccess(true);
        return data;
    }

    @Override
    public ResponseData getKhExceldata(MultipartFile file, KhOrderOutParam khOrderOutParam, ResponseData data) throws Exception {
        User user = userService.getcurrentUser();
        String fileName = file.getOriginalFilename();
        List<HxOrderOutExcelItem> list = new ArrayList<HxOrderOutExcelItem>();

        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            data.setMessage("上传文件格式不正确");
            data.setSuccess(false);
        }
        boolean isExcel2003 = true;
        if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
            isExcel2003 = false;
        }
        InputStream is = file.getInputStream();
        Workbook wb = null;
        if (isExcel2003) {
            wb = new HSSFWorkbook(is);
        } else {
            wb = new XSSFWorkbook(is);
        }
        Sheet sheet = wb.getSheetAt(0);
        // 获取客户信息
        BasicCustomer basicCustomer = basicCustomerService.getById(khOrderOutParam.getCustomerId());
        if (ToolUtil.isEmpty(basicCustomer)) {
            return ResponseData.error("客户不存在，无法操作");
        }

        BasicWarehouse basicWarehouse = basicWarehouseService.getById(khOrderOutParam.getSetWarehouseId());
        if (ToolUtil.isEmpty(basicWarehouse)) {
            return ResponseData.error("仓库不存在，无法操作");
        }

        // 商品信息
        List<HxBasicGoods> hxBasicGoodsList = hxBasicGoodsService.lambdaQuery()
                .eq(HxBasicGoods::getGoodsCustomerId, khOrderOutParam.getCustomerId())
                .list();
        Map<String, HxBasicGoods> hxBasicGoodsMap = new HashMap<>();
        for (HxBasicGoods obj : hxBasicGoodsList) {
            hxBasicGoodsMap.put(obj.getGoodsNumber(), obj);
        }

        // 门店信息
        List<BasicPutgoods> basicPutgoodsList = basicPutgoodsService.lambdaQuery()
                .eq(BasicPutgoods::getPutgoodsCustomerId, khOrderOutParam.getCustomerId())
                .eq(BasicPutgoods::getUnitType, "门店")
                .list();
        Map<String, BasicPutgoods> basicPutgoodsMap = new HashMap<>();
        for (BasicPutgoods obj : basicPutgoodsList) {
            basicPutgoodsMap.put(obj.getUnitNumber(), obj);
        }

        List<KhOrderOut> khOrderOutAddList = new ArrayList<>();
        List<KhOrderOutItem> khOrderOutItemAddList = new ArrayList<>();
        KhOrderOut khOrderOutAdd;
        KhOrderOutItem khOrderOutItemAdd;
        Map<String, KhOrderOut> khOrderOutMap = new HashMap<>();
        for (int r = 1; r <= sheet.getLastRowNum() + 1; r++) {
            Row row = sheet.getRow(r);
            if (row == null) {
                continue;
            }
            khOrderOutAdd = new KhOrderOut();
            khOrderOutItemAdd = new KhOrderOutItem();
            khOrderOutAdd.setCustomerId(basicCustomer.getId().longValue());
            khOrderOutAdd.setCustomerName(basicCustomer.getCustomerName());
            khOrderOutAdd.setOrderType(khOrderOutParam.getOrderType());
            khOrderOutAdd.setOrderState("待提交");
            khOrderOutAdd.setSetWarehouseId(basicWarehouse.getId().longValue());
            khOrderOutAdd.setSetWarehouse(basicWarehouse.getWarehouseName());
            khOrderOutAdd.setCreateUserName(user.getName());
            khOrderOutAdd.setCreateTime(new Date());
            khOrderOutAdd.setWorkType(khOrderOutParam.getWorkType());

            String peisongTime = getCellValue(row.getCell(46));
            if (ToolUtil.isNotEmpty(peisongTime)) {
                khOrderOutAdd.setPeisongTime(peisongTime);
            }

            String orderOutNumber = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(42));
            if (ToolUtil.isNotEmpty(orderOutNumber)) {
                khOrderOutAdd.setOrderOutNumber(orderOutNumber);
                khOrderOutItemAdd.setOrderOutNumber(orderOutNumber);
            } else {
                data.setMessage("订单号不能为空！");
                data.setSuccess(false);
                return data;
            }
            Integer count = this.lambdaQuery()
                    .eq(KhOrderOut::getCustomerId, khOrderOutParam.getCustomerId())
                    .eq(KhOrderOut::getOrderOutNumber, orderOutNumber)
                    .count();
            if (count > 0) {
                data.setMessage(orderOutNumber + "订单号已存在，不能重复导入！");
                data.setSuccess(false);
                return data;
            }
            // 收货客户编码
            String getUnitNumber = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(31));
            if (ToolUtil.isNotEmpty(getUnitNumber)) {
                khOrderOutAdd.setGetUnitNumber(getUnitNumber);
            } else {
                data.setMessage("收货客户编码不能为空！");
                data.setSuccess(false);
                return data;
            }
            if (ToolUtil.isEmpty(basicPutgoodsMap.get(getUnitNumber))) {
                data.setMessage(getUnitNumber + "收货客户不存在，请维护后重试！");
                data.setSuccess(false);
                return data;
            }
            khOrderOutAdd.setGetUnit(basicPutgoodsMap.get(getUnitNumber).getPutgoodsName());
            khOrderOutAdd.setGetUnitId(basicPutgoodsMap.get(getUnitNumber).getId().longValue());

            // 商品编码
            String goodsNumber = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(9));
            if (ToolUtil.isNotEmpty(goodsNumber)) {
                khOrderOutItemAdd.setGoodsNumber(goodsNumber);
            } else {
                data.setMessage("收货客户编码不能为空！");
                data.setSuccess(false);
                return data;
            }
            HxBasicGoods hxBasicGoods = hxBasicGoodsMap.get(goodsNumber);
            if (ToolUtil.isEmpty(hxBasicGoods)) {
                return ResponseData.error(goodsNumber + "商品不存在，请维护后重试！");
            }
            khOrderOutItemAdd.setGoodsId(hxBasicGoods.getId().longValue());
            khOrderOutItemAdd.setGoodsName(hxBasicGoods.getGoodsName());
            khOrderOutItemAdd.setGoodsHcode69(hxBasicGoods.getGoodsHcode69());
            khOrderOutItemAdd.setGoodsXcode69(hxBasicGoods.getGoodsXcode69());
            khOrderOutItemAdd.setGoodsPcode69(hxBasicGoods.getGoodsPcode69());
            khOrderOutItemAdd.setGoodsSpecs(hxBasicGoods.getGoodsSpecs());
            khOrderOutItemAdd.setDismantlingParts(hxBasicGoods.getDismantlingParts());
            khOrderOutItemAdd.setUnit(hxBasicGoods.getPsUnit());
            khOrderOutItemAdd.setRate(new BigDecimal(hxBasicGoods.getPsRate()));
            // 商品数量
            String psNum = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(11));
            if (ToolUtil.isNotEmpty(psNum)) {
                khOrderOutItemAdd.setPsNum(new BigDecimal(psNum));
            } else {
                data.setMessage("梳理不能为空！");
                data.setSuccess(false);
                return data;
            }
            khOrderOutItemAdd.setJibenUnit(hxBasicGoods.getJibenUnit());
            khOrderOutItemAdd.setJibenNum(khOrderOutItemAdd.getPsNum().multiply(khOrderOutItemAdd.getRate()));

            khOrderOutItemAddList.add(khOrderOutItemAdd);

            if (ToolUtil.isEmpty(khOrderOutMap.get(orderOutNumber))) {
                khOrderOutAddList.add(khOrderOutAdd);
                khOrderOutMap.put(orderOutNumber, khOrderOutAdd);
            }
        }

        if (khOrderOutAddList.size() > 0 && khOrderOutItemAddList.size() > 0) {
            khOrderOutItemService.saveBatch(khOrderOutItemAddList);
            this.saveBatch(khOrderOutAddList);
        }
        data.setSuccess(true);
        return data;
    }

    @Override
    @Transactional(rollbackFor = Exception.class, timeout = 600)
    public ResponseData downDps(DownDpsIby downDpsIby) throws Exception {
        User user = userService.getcurrentUser();
        // 根据批次 查询对应门店信息
        List<KhWare> khWareList = khWareService.lambdaQuery()
                .eq(KhWare::getWaveNum, downDpsIby.getWaveNum())
                .eq(KhWare::getOperateId, user.getOperateId())
                .list();
        if (ToolUtil.isEmpty(khWareList)) {
            return ResponseData.error("暂无波次数据，请先维护后重试！！");
        }
        Map<String, KhWare> khWareMap = new HashMap<>();
        for (KhWare obj : khWareList) {
            khWareMap.put(obj.getGetUnitNumber(), obj);
        }
        // 根据 仓库 和工作模式 并且未下发DPS
        List<KhOrderOut> khOrderOutList = this.lambdaQuery()
                .eq(KhOrderOut::getSetWarehouseId, downDpsIby.getSetWarehouseId())
                .eq(KhOrderOut::getWorkType, downDpsIby.getWorkType())
                .in(KhOrderOut::getGetUnitNumber, khWareMap.keySet())
                .eq(KhOrderOut::getOrderType,downDpsIby.getOrderType())
                .isNull(KhOrderOut::getDpsDownTime)
                .list();

        if (ToolUtil.isEmpty(khOrderOutList)) {
            return ResponseData.error("暂无下发数据！！");
        }
        Map<String, KhOrderOut> khOrderOutMap = new HashMap<>();
        Map<String, KhWare> orderOutWareMap = new HashMap<>();
        Set<Integer> idSet = new HashSet<>();
        for (KhOrderOut obj : khOrderOutList) {
            idSet.add(obj.getId());
            khOrderOutMap.put(obj.getOrderOutNumber(), obj);
            orderOutWareMap.put(obj.getOrderOutNumber(), khWareMap.get(obj.getGetUnitNumber()));
        }
        List<KhOrderOutItem> khOrderOutItemList = khOrderOutItemService.lambdaQuery()
                .in(KhOrderOutItem::getOrderOutNumber, khOrderOutMap.keySet())
                .list();

        DpsZcServer dpsZcServer;
        if ("冷藏".equals(downDpsIby.getWorkType())) {
            dpsZcServer = dpsZcServerService.lambdaQuery()
                    .eq(DpsZcServer::getOperateId, user.getOperateId())
                    .eq(DpsZcServer::getWorkTyp, "播种")
                    .eq(DpsZcServer::getAreaId, 0)
                    .one();
        } else {
            dpsZcServer = dpsZcServerService.lambdaQuery()
                    .eq(DpsZcServer::getOperateId, user.getOperateId())
                    .eq(DpsZcServer::getWorkTyp, "播种")
                    .eq(DpsZcServer::getAreaId, 1)
                    .one();
        }

        String dateBch = cn.stylefeng.guns.core.util.DateUtil.getDateBch();
        String dateS = cn.stylefeng.guns.core.util.DateUtil.getDateS();
        String matchid = dateBch + downDpsIby.getWaveNum();
        if("直流".equals(downDpsIby.getOrderType())){
            matchid = matchid + "0";
        }else{
            matchid = matchid + "1";
        }
        // 下发到sqlserver数据库
        Connection conn = null;
        PreparedStatement psInsert = null;
        try {
            conn = MssqlUtils.getConnection(dpsZcServer.getIp(), dpsZcServer.getPort(), dpsZcServer.getDbName(), dpsZcServer.getDbUser(), dpsZcServer.getDbPwd());
            conn.setAutoCommit(false);
            psInsert = conn.prepareStatement(this.getInsertSql(downDpsIby.getWorkType()));
            for (KhOrderOutItem obj : khOrderOutItemList) {
                psInsert.setString(1, obj.getId().toString());
                psInsert.setString(2, obj.getGoodsNumber());
                psInsert.setString(3, matchid);
                psInsert.setInt(4, obj.getPsNum().intValue());
                psInsert.setString(5, orderOutWareMap.get(obj.getOrderOutNumber()).getToLocation());
                psInsert.setString(6, orderOutWareMap.get(obj.getOrderOutNumber()).getGetUnitSort());
                psInsert.setString(7, "0");
                psInsert.setString(8, dateS);
                psInsert.setString(9, orderOutWareMap.get(obj.getOrderOutNumber()).getGetUnitNumber());
                psInsert.setString(10, obj.getGoodsSpecs());

                StringBuffer barcodeStr = new StringBuffer();
                if (ToolUtil.isNotEmpty(obj.getGoodsPcode69())) {
                    barcodeStr.append(obj.getGoodsPcode69());
                }
                if (ToolUtil.isNotEmpty(obj.getGoodsHcode69())) {
                    if (barcodeStr.length() > 0) {
                        barcodeStr.append(",").append(obj.getGoodsHcode69());
                    } else {
                        barcodeStr.append(obj.getGoodsHcode69());
                    }
                }
                if (ToolUtil.isNotEmpty(obj.getGoodsXcode69())) {
                    if (barcodeStr.length() > 0) {
                        barcodeStr.append(",").append(obj.getGoodsXcode69());
                    } else {
                        barcodeStr.append(obj.getGoodsXcode69());
                    }
                }
                psInsert.setString(11, barcodeStr.toString());
                psInsert.setString(12, obj.getGoodsName());
                psInsert.setString(13, orderOutWareMap.get(obj.getOrderOutNumber()).getGetUnit());
                psInsert.addBatch();
            }
            if (khOrderOutItemList.size() > 0) {
                psInsert.executeBatch();
            }
            this.lambdaUpdate()
                    .in(KhOrderOut::getId, idSet)
                    .set(KhOrderOut::getDpsDownTime, dateS)
                    .set(KhOrderOut::getDpsUserAct, user.getAccount())
                    .update();
            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
            JdbcUtls.rollback(conn);
            throw new Exception(e.getMessage());
        } finally {
            JdbcUtls.closeAll(null, psInsert, conn);
        }

        return ResponseData.success();
    }

    @Override
    @Transactional
    public ResponseData gxTz(Long[] ids) throws Exception {
        User user = userService.getcurrentUser();
        List<KhOrderOut> khOrderOutList = this.lambdaQuery()
                .in(KhOrderOut::getId, ids)
                .last("for update ")
                .list();
        if (ToolUtil.isEmpty(khOrderOutList)) {
            return ResponseData.error("请选择要提总的订单");
        }
        List<String> orderOutNumberList = new ArrayList<>();
        Map<String, KhOrderOut> khOrderOutMap = new HashMap<>();
        String _setWarehouseId = "";
        String _setWarehouseName = "";
        for (KhOrderOut obj : khOrderOutList) {
            if (!"".equals(_setWarehouseId) && !_setWarehouseId.equals(obj.getSetWarehouseId().toString())) {
                return ResponseData.error("请选择相同仓库的订单");
            }
            if(!"待提交".equals(obj.getOrderState())){
                return ResponseData.error("请选择[待提交]状态订单");
            }
            _setWarehouseId = obj.getSetWarehouseId().toString();
            _setWarehouseName = obj.getSetWarehouse();
            orderOutNumberList.add(obj.getOrderOutNumber());
            khOrderOutMap.put(obj.getOrderOutNumber(), obj);
        }

        // 根据订单汇总商品明细
        Map<String, KhOrderOutItemResult> goodsSumMap = new HashMap<>();
        List<KhOrderOutItemResult> goodsSumList = khOrderOutItemService.getGoodsSum(orderOutNumberList);
        for (KhOrderOutItemResult obj : goodsSumList) {
            goodsSumMap.put(obj.getGoodsId().toString(), obj);
        }

        // 查询涉及的商品基本信息
        List<HxBasicGoods> hxBasicGoodslist = hxBasicGoodsService.lambdaQuery()
                .in(HxBasicGoods::getId, goodsSumMap.keySet())
                .list();

        Map<String,HxBasicGoods> hxBasicGoodsMap = new HashMap<>();
        for(HxBasicGoods obj : hxBasicGoodslist){
            hxBasicGoodsMap.put(obj.getId().toString(), obj);
        }
        // 判断商品信息是否存在
        for(String goodsIdStr : goodsSumMap.keySet()){
            if(ToolUtil.isEmpty(hxBasicGoodsMap.get(goodsIdStr))){
                return ResponseData.error("商品["+goodsSumMap.get(goodsIdStr).getGoodsNumber()+"}在商品档案中不存在！");
            }
        }

        // 将涉及的商品库存，按照生产日期 排序
        List<HxStorageStock> hxStorageStockList = hxStorageStockService
                .lambdaQuery()
                .in(HxStorageStock::getGoodsId, goodsSumMap.keySet())
                .eq(HxStorageStock::getWarehouseId, _setWarehouseId)
                .eq(HxStorageStock::getStockState,"正常")
                .orderByAsc(HxStorageStock::getDetailedDate)
                .last("for update ")
                .list();



        // 按照 商品封装需要数据
        Map<String,List<HxStorageStock>> hxStorageStockMap = new HashMap<>();
        List<HxStorageStock> _hxStorageStockList;
        Map<String,BigDecimal> goodsKcSumMap = new HashMap<>();
        Set<Long> PositionIdSet = new HashSet<>();
        for(HxStorageStock obj : hxStorageStockList){
            PositionIdSet.add(obj.getPositionId());
            if(ToolUtil.isEmpty(hxStorageStockMap.get(obj.getGoodsId().toString()))){
                _hxStorageStockList = new ArrayList<>();
                _hxStorageStockList.add(obj);
                hxStorageStockMap.put(obj.getGoodsId().toString(),_hxStorageStockList);
                goodsKcSumMap.put(obj.getGoodsId().toString(),obj.getJibenNum());
            }else{
                hxStorageStockMap.get(obj.getGoodsId().toString()).add(obj);
                goodsKcSumMap.put(obj.getGoodsId().toString(),goodsKcSumMap.get(obj.getGoodsId().toString()).add(obj.getJibenNum()));
            }
        }

        // 判断库存是否满足当前任务
        for(String goodsIdStr : goodsSumMap.keySet()){
            if(ToolUtil.isEmpty(goodsKcSumMap.get(goodsIdStr))){
                return ResponseData.error("商品["+goodsSumMap.get(goodsIdStr).getGoodsNumber()+"]无库存");
            }
            BigDecimal subtract = goodsSumMap.get(goodsIdStr).getJibenNum().subtract(goodsKcSumMap.get(goodsIdStr));
            if(subtract.compareTo(BigDecimal.ZERO) > 0){
                return ResponseData.error("商品["+goodsSumMap.get(goodsIdStr).getGoodsNumber()+"]库存缺少："+subtract+goodsSumMap.get(goodsIdStr).getJibenUnit());
            }
        }

        // 查询涉及的库位
        List<BasicPosition> basicPositionList = basicPositionService.lambdaQuery()
                .in(BasicPosition::getId, PositionIdSet)
                .list();
        Map<String,BasicPosition> basicPositionMap = new HashMap<>();
        for(BasicPosition obj : basicPositionList){
            basicPositionMap.put(obj.getId().toString(),obj);
        }



        // 生成对应的分拣任务及库存指定
        String datesBch = cn.stylefeng.guns.core.util.DateUtil.getDatesBch();
        String orderNumber = "TZ"+datesBch;
        for(KhOrderOutItemResult obj : goodsSumList){
            HxBasicGoods goodsInfo = hxBasicGoodsMap.get(obj.getGoodsId().toString());
            KhTzStorageOrderParam khTzStorageOrderAdd = new KhTzStorageOrderParam();
            khTzStorageOrderAdd.setGoodsId(obj.getGoodsId());
            khTzStorageOrderAdd.setGoodsName(goodsInfo.getGoodsName());
            khTzStorageOrderAdd.setGoodsNumber(goodsInfo.getGoodsNumber());
            khTzStorageOrderAdd.setDismantlingParts(goodsInfo.getDismantlingParts());
            khTzStorageOrderAdd.setPsNum(obj.getPsNum());
            khTzStorageOrderAdd.setRate(new BigDecimal(goodsInfo.getPsRate()));
            khTzStorageOrderAdd.setUnit(goodsInfo.getPsUnit());
            khTzStorageOrderAdd.setJibenNum(obj.getJibenNum());
            khTzStorageOrderAdd.setCreateUserName(user.getName());
            khTzStorageOrderAdd.setCreateTime(new Date());
            khTzStorageOrderAdd.setOrderState("拣货中");
            khTzStorageOrderAdd.setOrderNumber(orderNumber);
            khTzStorageOrderAdd.setSetWarehouseId(Long.valueOf(_setWarehouseId));
            khTzStorageOrderAdd.setSetWarehouse(_setWarehouseName);
            khTzStorageOrderService.add(khTzStorageOrderAdd);
            BigDecimal jbSum = obj.getJibenNum();
            for(HxStorageStock _hxStorageStock : hxStorageStockMap.get(obj.getGoodsId().toString())){
                BigDecimal syNum = jbSum.subtract(_hxStorageStock.getJibenNum());
                KhTzStorageOrderItemParam khTzStorageOrderItemAdd = new KhTzStorageOrderItemParam();
                khTzStorageOrderItemAdd.setGoodsId(obj.getGoodsId());
                if(ToolUtil.isNotEmpty(khTzStorageOrderAdd.getId())){
                    khTzStorageOrderItemAdd.setTzStorageOrderId(khTzStorageOrderAdd.getId());
                }
                khTzStorageOrderItemAdd.setOrderNumber(orderNumber);
                khTzStorageOrderItemAdd.setGoodsName(goodsInfo.getGoodsName());
                khTzStorageOrderItemAdd.setGoodsNumber(goodsInfo.getGoodsNumber());
                khTzStorageOrderItemAdd.setGoodsSpecs(goodsInfo.getGoodsSpecs());
                khTzStorageOrderItemAdd.setGoodsHcode69(goodsInfo.getGoodsHcode69());
                khTzStorageOrderItemAdd.setGoodsPcode69(goodsInfo.getGoodsPcode69());
                khTzStorageOrderItemAdd.setGoodsXcode69(goodsInfo.getGoodsXcode69());
                khTzStorageOrderItemAdd.setDismantlingParts(goodsInfo.getDismantlingParts());
                khTzStorageOrderItemAdd.setUnit(goodsInfo.getPsUnit());
                khTzStorageOrderItemAdd.setJibenUnit(goodsInfo.getJibenUnit());
                khTzStorageOrderItemAdd.setStorageState("待拣货");
                BasicPosition basicPosition = basicPositionMap.get(_hxStorageStock.getPositionId().toString());
                if( basicPosition != null && ToolUtil.isNotEmpty(basicPosition.getPositionPickingorder())){
                    khTzStorageOrderItemAdd.setPositionPickingorder(Integer.valueOf(basicPosition.getPositionPickingorder()));
                }else{
                    khTzStorageOrderItemAdd.setPositionPickingorder(0);
                }

                if(syNum.compareTo(BigDecimal.ZERO) > 0){
                    khTzStorageOrderItemAdd.setJibenNum(_hxStorageStock.getJibenNum());
                    khTzStorageOrderItemAdd.setPsNum(_hxStorageStock.getJibenNum().divide(new BigDecimal(goodsInfo.getPsRate()),2,BigDecimal.ROUND_HALF_DOWN));
                    khTzStorageOrderItemAdd.setStockId(_hxStorageStock.getId().longValue());
                    khTzStorageOrderItemAdd.setPositionNumber(_hxStorageStock.getPositionNumber());
                    khTzStorageOrderItemService.add(khTzStorageOrderItemAdd);
                    hxStorageStockService.lambdaUpdate()
                            .eq(HxStorageStock::getId,_hxStorageStock.getId())
                            .set(HxStorageStock::getStockState,"提总")
                            .update();
                }else if(syNum.compareTo(BigDecimal.ZERO) == 0){
                    khTzStorageOrderItemAdd.setJibenNum(_hxStorageStock.getJibenNum());
                    khTzStorageOrderItemAdd.setPsNum(_hxStorageStock.getJibenNum().divide(new BigDecimal(goodsInfo.getPsRate()),2,BigDecimal.ROUND_HALF_DOWN));
                    khTzStorageOrderItemAdd.setStockId(_hxStorageStock.getId().longValue());
                    khTzStorageOrderItemAdd.setPositionNumber(_hxStorageStock.getPositionNumber());
                    khTzStorageOrderItemService.add(khTzStorageOrderItemAdd);
                    hxStorageStockService.lambdaUpdate()
                            .eq(HxStorageStock::getId,_hxStorageStock.getId())
                            .set(HxStorageStock::getStockState,"提总")
                            .update();
                    break;
                }else{ // 拆库存
                    // 新增库存
                    HxStorageStock addHxStorageStock = new HxStorageStock();
                    ToolUtil.copyProperties(_hxStorageStock,addHxStorageStock);
                    addHxStorageStock.setJibenNum(jbSum);
                    addHxStorageStock.setKcNum(jbSum.divide(new BigDecimal(_hxStorageStock.getKcRate()),2,BigDecimal.ROUND_HALF_DOWN).toString());
                    addHxStorageStock.setStockState("提总");
                    addHxStorageStock.setId(null);
                    hxStorageStockService.save(addHxStorageStock);

                    // 新增体总分拣任务
                    khTzStorageOrderItemAdd.setJibenNum(addHxStorageStock.getJibenNum());
                    khTzStorageOrderItemAdd.setPsNum(jbSum.divide(new BigDecimal(goodsInfo.getPsRate()),2,BigDecimal.ROUND_HALF_DOWN));
                    khTzStorageOrderItemAdd.setStockId(addHxStorageStock.getId().longValue());
                    khTzStorageOrderItemAdd.setPositionNumber(_hxStorageStock.getPositionNumber());
                    khTzStorageOrderItemService.add(khTzStorageOrderItemAdd);

                    // 修改库存数量
                    hxStorageStockService.lambdaUpdate()
                            .eq(HxStorageStock::getId,_hxStorageStock.getId())
                            .set(HxStorageStock::getJibenNum,syNum.abs())
                            .set(HxStorageStock::getKcNum,syNum.abs().divide(new BigDecimal(_hxStorageStock.getKcRate()),2,BigDecimal.ROUND_HALF_DOWN).toString())
                            .update();
                    break;
                }
                jbSum = syNum;
            }
        }
        // 修改提总单状态为 已提交
        this.lambdaUpdate()
                .in(KhOrderOut::getId,ids)
                .set(KhOrderOut::getOrderState,"已提交")
                .update();

        return ResponseData.success();
    }

    private String getInsertSql(String workTyp) {
        StringBuffer sql = new StringBuffer();
        sql.append("insert into MidOrders_L(");
        sql.append("orderid");
        sql.append(",sku");
        sql.append(",matchid");
        sql.append(",quantity");
        sql.append(",tolocation");
        sql.append(",area");
        sql.append(",batchid");
        sql.append(",state");
        sql.append(",downdate");
        sql.append(",shopid");
        sql.append(",discount");
        sql.append(",barcode");
        sql.append(",goodsname");
        sql.append(",shopname");
        sql.append(") values(");
        sql.append("?");
        sql.append(",?");
        sql.append(",?");
        sql.append(",?");
        sql.append(",?");
        if ("冷藏".equals(workTyp)) {
            sql.append(",'L1' ");
        } else {
            sql.append(",'L2' ");
        }
        sql.append(",?");
        sql.append(",?");
        sql.append(",?");
        sql.append(",?");
        sql.append(",?");
        sql.append(",?");
        sql.append(",?");
        sql.append(",?");
        sql.append(") ");
        return sql.toString();
    }

    private Serializable getKey(KhOrderOutParam param) {
        return param.getId();
    }

    private Page getPageContext() {
        return LayuiPageFactory.defaultPage();
    }

    private KhOrderOut getOldEntity(KhOrderOutParam param) {
        return this.getById(getKey(param));
    }

    private KhOrderOut getEntity(KhOrderOutParam param) {
        KhOrderOut entity = new KhOrderOut();
        ToolUtil.copyProperties(param, entity);
        return entity;
    }


    private static String getCellValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            if (DateUtil.isCellDateFormatted(cell)) {
                return HSSFDateUtil.getJavaDate(cell.getNumericCellValue()).toString();
            } else {
                return new BigDecimal(Double.toString(cell.getNumericCellValue())).toString();
            }
        } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
            return StringUtils.trimToEmpty(cell.getStringCellValue());
        } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
            return StringUtils.trimToEmpty(cell.getCellFormula());
        } else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
            return "";
        } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(cell.getBooleanCellValue());
        } else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
            return "ERROR";
        } else {
            return cell.toString().trim();
        }
    }

}
